package com.jplus.framework.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.jplus.framework.InstanceFactory;
import com.jplus.framework.util.ClassUtil;
import com.jplus.framework.util.FormatUtil;

/***
 * JDBCTemplate<br>
 * 线程安全的。 2015-12-07 10:48:36
 * 
 * @author yuanqy
 */
public final class JdbcTemplate {
	private String jdbcUrl;
	private String user;
	private String password;
	private Logger log = Logger.getLogger(JdbcTemplate.class);
	private DataSource datasource;
	private ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
	private ThreadLocal<PreparedStatement> pstmt = new ThreadLocal<PreparedStatement>();
	private ThreadLocal<ResultSet> resultSet = new ThreadLocal<ResultSet>();

	private boolean tx = false;// 是否开启事物

	public JdbcTemplate() {
		this.datasource = InstanceFactory.getDataSourceFactory().getDataSource();
	}

	public JdbcTemplate(DataSource datasource) {
		this.datasource = datasource;
	}

	public JdbcTemplate(String jdbcUrl, String user, String password) throws ClassNotFoundException {
		init("com.mysql.jdbc.Driver", jdbcUrl, user, password);
	}

	public JdbcTemplate(String driverClass, String jdbcUrl, String user, String password) throws ClassNotFoundException {
		init(driverClass, jdbcUrl, user, password);
	}

	private void init(String driverClass, String jdbcUrl, String user, String password) throws ClassNotFoundException {
		try {
			Class.forName(driverClass);
			this.jdbcUrl = jdbcUrl;
			this.user = user;
			this.password = password;
		} catch (Exception e) {
			throw new ClassNotFoundException("找不到驱动程序类 [" + driverClass + "]，加载驱动失败！");
		}
	}

	/**
	 * 增加、删除、改
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public boolean update(String sql, Object... params) throws Exception {
		boolean flag = false;
		try {
			initConnect();
			initParam(sql, params);
			int result = pstmt.get().executeUpdate();
			flag = result > 0 ? true : false;
		} catch (Exception e) {
			throw e;
		} finally {
			destroy();
		}
		return flag;
	}

	/**
	 * 查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> query(String sql, Object... params) throws Exception {
		Map<String, Object> map = null;
		try {
			initConnect();
			initParam(sql, params);
			resultSet.set(pstmt.get().executeQuery());// 返回查询结果
			ResultSet rset = resultSet.get();
			ResultSetMetaData metaData = rset.getMetaData();
			map = new HashMap<String, Object>();
			while (rset.next()) {
				map = getMap(rset, metaData);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			destroy();
		}
		return map;
	}

	/**
	 * 查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryList(String sql, Object... params) throws Exception {
		List<Map<String, Object>> list = null;
		try {
			initConnect();
			initParam(sql, params);
			resultSet.set(pstmt.get().executeQuery());
			ResultSet rset = resultSet.get();
			ResultSetMetaData metaData = rset.getMetaData();
			list = new ArrayList<Map<String, Object>>();
			while (rset.next()) {
				list.add(getMap(rset, metaData));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			destroy();
		}
		return list;
	}

	/**
	 * 通过反射机制查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> T queryBean(String sql, Class<T> cls, Object... params) throws Exception {
		T resultObject = null;
		try {
			initConnect();
			initParam(sql, params);
			resultSet.set(pstmt.get().executeQuery());
			ResultSet rset = resultSet.get();
			ResultSetMetaData metaData = rset.getMetaData();
			while (rset.next()) {
				resultObject = getBean(rset, metaData, cls);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			destroy();
		}
		return resultObject;
	}

	/**
	 * 通过反射机制查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @param cls
	 * @return
	 * @throws Exception
	 */
	public <T> List<T> queryListBean(String sql, Class<T> cls, Object... params) throws Exception {
		List<T> list = null;
		try {
			initConnect();
			initParam(sql, params);
			resultSet.set(pstmt.get().executeQuery());
			ResultSet rset = resultSet.get();
			ResultSetMetaData metaData = rset.getMetaData();
			list = new ArrayList<T>();
			while (rset.next()) {
				list.add(getBean(rset, metaData, cls));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			destroy();
		}
		return list;
	}

	/**
	 * 释放数据库连接
	 */
	public void destroy() {
		if (!tx) {
			if (resultSet.get() != null) { // 关闭记录集
				try {
					resultSet.get().close();
					resultSet.remove();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt.get() != null) { // 关闭声明
				try {
					pstmt.get().close();
					pstmt.remove();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection.get() != null) { // 关闭连接对象
				try {
					connection.get().close();
					connection.remove();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 开启事物
	 * 
	 * @throws SQLException
	 */
	public void openTransaction() throws SQLException {
		tx = true;
		initConnect();
		connection.get().setAutoCommit(false);
		// connection.setTransactionIsolation(TransactionIsolationLevel.REPEATABLE_READ.getLevel());
	}

	public void openTransaction(int isolationLevel) throws SQLException {
		openTransaction();
		connection.get().setTransactionIsolation(isolationLevel);
	}

	/** 事物回滚 */
	public void rollback() {
		dotx(true);
	}

	/** 事物提交 */
	public void commit() {
		dotx(false);
	}

	private void dotx(boolean bo) {
		if (connection.get() != null) {
			try {
				if (bo)
					connection.get().rollback();
				else
					connection.get().commit();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx = false;
			destroy();
		}
	}

	/**
	 * 获得数据库的连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection initConnect() throws SQLException {
		if (connection.get() == null || connection.get().isClosed()) {
			try {
				if (datasource != null)
					connection.set(datasource.getConnection());
				else
					connection.set(DriverManager.getConnection(jdbcUrl, user, password));
			} catch (SQLException e) {
				log.error("JDBC连接失败:" + e.getMessage());
				throw e;
			}
		}
		return connection.get();
	}

	/**
	 * 拼接参数
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	private PreparedStatement initParam(String sql, Object... params) throws SQLException {
		int index = 1;
		pstmt.set(connection.get().prepareStatement(sql));
		if (params != null && params.length >= 0) {
			for (int i = 0; i < params.length; i++) {
				pstmt.get().setObject(index++, params[i]);
			}
		}
		return pstmt.get();
	}

	private Map<String, Object> getMap(ResultSet rset, ResultSetMetaData metaData) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		int col_len = metaData.getColumnCount();
		for (int i = 0; i < col_len; i++) {
			int type = metaData.getColumnType(i + 1);
			String cols_name = metaData.getColumnName(i + 1);
			map.put(cols_name, getValue(rset, String.class, type, cols_name));
		}
		return map;
	}

	private <T> T getBean(ResultSet rset, ResultSetMetaData metaData, Class<T> cls) throws SQLException, NoSuchFieldException, SecurityException,
			InstantiationException, IllegalAccessException {
		// 通过反射机制创建一个实例
		T bean = cls.newInstance();
		int col_len = metaData.getColumnCount();
		for (int i = 0; i < col_len; i++) {
			int ctype = metaData.getColumnType(i + 1);
			String cols_name = metaData.getColumnName(i + 1);
			Field field = cls.getDeclaredField(cols_name);
			field.setAccessible(true); // 打开javabean的访问权限
			Object cols_value = getValue(rset, field.getType(), ctype, cols_name);
			field.set(bean, cols_value);
		}
		return bean;
	}

	private Object getValue(ResultSet rset, Class<?> type, int ctype, String cols_name) throws SQLException {
		if (ClassUtil.isInt(type)) {
			return FormatUtil.toInt(rset.getObject(cols_name));
		} else if (ClassUtil.isLong(type)) {
			return FormatUtil.toLong(rset.getObject(cols_name));
		} else if (ClassUtil.isDouble(type)) {
			return FormatUtil.toDouble(rset.getObject(cols_name));
		} else if (ClassUtil.isDecimal(type)) {
			return FormatUtil.toDecimal(rset.getObject(cols_name));
		} else if (ClassUtil.isString(type)) {
			if (Types.TIMESTAMP == ctype) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
				Timestamp tt = rset.getTimestamp(cols_name);
				if (tt == null)
					return null;
				return sdf.format(rset.getTimestamp(cols_name));
			}
			return FormatUtil.toString(rset.getObject(cols_name));
		} else {
			throw new ClassCastException("Can't cast this class type:[" + type + "]");
		}
	}

	/**
	 * 并发压力测试
	 * 
	 * @param args
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		ExecutorService es = Executors.newFixedThreadPool(10);
		final JdbcTemplate jd = new JdbcTemplate("jdbc:mysql://localhost:3306/pay", "root", "root");
		Thread t = new Thread() {
			@Override
			public void run() {
				// System.out.println(this.getName());
				try {
					if (Math.round(Math.random()) == 1) {
						System.out.println("select");
						jd.query("select * from sys_job where id=1002");
					} else {
						System.out.println("update");
						jd.update("UPDATE sys_job SET state=? WHERE id=1002", Math.round(Math.random()));
					}
				} catch (Exception e) {
					System.out.println(e.getMessage());
				}
			}
		};
		for (int j = 0; j < 10000; j++) {
			es.execute(t);
		}
	}
}
